package com.etone.smartAudit.utils.word;

import com.etone.daemon.db.helper.QueryHelper;
import com.etone.smartAudit.enums.DataBaseType;
import com.etone.smartAudit.utils.DateUtil;
import com.etone.smartAudit.utils.NumberUtil;
import com.etone.smartAudit.utils.ObjectUtil;
import com.etone.smartAudit.utils.ZipUtil;
import com.etone.smartAudit.utils.database.DatabaseUtil;
import com.etone.smartAudit.vo.auditException.AuditExceptionByteArrayOutputStream;
import lombok.extern.log4j.Log4j2;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 地市风险报告解析map
 *
 * @author: sovy
 * @date: 2021/3/23
 * @description:
 */
@Log4j2
public class AuditExceptionForeignReportUtil {

    private String gpSchema;

    private String ip;

    private String user;

    private String password;

    private Integer port;

    private String database;

    private static String templateFilePath;

    /**
     * 模板缺省值
     */
    private static String defaultResourceTemplateFilePath = "word/areaExceptionReportTemplate.docx";

    @Value("${audit.word.areaExceptionReportPath:}")
    public void setDefaultFilePath(String templatePath) {
        AuditExceptionForeignReportUtil.templateFilePath = templatePath;
    }

    /**
     * 获取模板文件流
     *
     * @return
     * @throws IOException
     */
    public static InputStream readStream() throws IOException {
        if (StringUtils.isEmpty(templateFilePath)) {
            ClassPathResource classPathResource = new ClassPathResource(defaultResourceTemplateFilePath);
            return classPathResource.getInputStream();
        }
        return new FileInputStream(templateFilePath);
    }

    public static MultipartFile batchExport(String ip, String user, String password, Integer port, String database, String gpSchema, String startTime, String endTime, List<String> citys,String fileName) {
        List<Map<String, Object>> datas = new ArrayList<>();
        Date dateStartTime = DateUtil.turnStrDateToJavaUtilDate(startTime, "yyyy-MM");
        Date dateEndTime = DateUtil.turnStrDateToJavaUtilDate(endTime, "yyyy-MM");
        for (String city : citys) {
            AuditExceptionForeignReportUtil auditExceptionForeignReportUtil = new AuditExceptionForeignReportUtil(ip, user, password, port, database, gpSchema);
            Map<String, Object> map = new HashMap<>();
            map.put("city", city);
            map.put("reportStartTime", DateUtil.turnJavaUtilDateToStrDate(dateStartTime, "yyyy年MM月"));
            map.put("reportEndTime", DateUtil.turnJavaUtilDateToStrDate(dateEndTime, "yyyy年MM月"));
            for (int i = 1; i <= 7; i++) {
                map.put("reportEndTimeReduce" + i, DateUtil.turnJavaUtilDateToStrDate(DateUtil.parseAddDate(dateEndTime, Calendar.MONTH, -i), "yyyy年MM月"));
            }

            datas.add(auditExceptionForeignReportUtil.parseParams(map, city, DateUtil.turnJavaUtilDateToStrDate(dateStartTime, "yyyyMM"), DateUtil.turnJavaUtilDateToStrDate(dateEndTime, "yyyyMM")));
        }
        List<AuditExceptionByteArrayOutputStream> outputStreams = batchWriteStream(datas);
        return ZipUtil.ioTOMultipartFile(outputStreams, fileName);
    }

    public static List<AuditExceptionByteArrayOutputStream> batchWriteStream(List<Map<String, Object>> datas) {
        List<AuditExceptionByteArrayOutputStream> outputStreams = new ArrayList<>();
        for (Map<String, Object> data : datas) {
            XWPFDocument xwpfDocument = null;
            InputStream inputStream = null;
            try {
                inputStream = readStream();
                xwpfDocument = new XWPFDocument(inputStream);

                List<String> paragraphTargets = getRemoveParagraphTarget(xwpfDocument, data);
                remove(xwpfDocument, paragraphTargets);

                AuditExceptionByteArrayOutputStream outputStream = new AuditExceptionByteArrayOutputStream();
                outputStream.setFileName(data.get("city") + "地市风险扫描报告.docx");
                replaceParams(xwpfDocument, data);
                xwpfDocument.write(outputStream);
                outputStreams.add(outputStream);
            } catch (IOException ioException) {
                ioException.printStackTrace();
            } finally {
                if (xwpfDocument != null) {
                    try {
                        xwpfDocument.close();
                    } catch (IOException ioException) {
                        ioException.printStackTrace();
                    }
                }
                if (inputStream != null) {
                    try {
                        inputStream.close();
                    } catch (IOException ioException) {
                        ioException.printStackTrace();
                    }
                }
            }
        }
        return outputStreams;
    }

    public static void replaceParams(XWPFDocument xwpfDocument, Map<String, Object> dataMap) {
        List<XWPFParagraph> paragraphs = xwpfDocument.getParagraphs();
        List<XWPFTable> tables = xwpfDocument.getTables();

        String regex = "\\$\\{(.*?)}";
        Pattern pattern = Pattern.compile(regex);

        log.info("------------------paragraph------------------");
        //替换段落内容
        for (XWPFParagraph paragraph : paragraphs) {
            List<XWPFRun> runs = paragraph.getRuns();
            for (XWPFRun run : runs) {
                String text = run.text();
                run.setText(replaceText(pattern, text, dataMap), 0);
            }
        }
        log.info("------------------table------------------");
        for (XWPFTable table : tables) {
            List<XWPFTableRow> rows = table.getRows();
            for (XWPFTableRow row : rows) {
                List<XWPFTableCell> tableCells = row.getTableCells();
                for (XWPFTableCell tableCell : tableCells) {
                    List<XWPFParagraph> cellParagraphs = tableCell.getParagraphs();
                    for (XWPFParagraph cellParagraph : cellParagraphs) {
                        List<XWPFRun> runs = cellParagraph.getRuns();
                        for (XWPFRun run : runs) {
                            String text = run.text();
                            run.setText(replaceText(pattern, text, dataMap), 0);
                        }
                    }
                }
            }
        }

    }

    public static String replaceText(Pattern pattern, String text, Map<String, Object> dataMap) {
        Matcher matcher = pattern.matcher(text);
        while (matcher.find()) {
            String group = matcher.group(1);
            if (dataMap.containsKey(group)) {

                String replaceValue = String.valueOf(dataMap.get(group));
                text = text.replaceAll("\\$\\{" + group + "\\}", replaceValue);
            } else {
                continue;
            }
            matcher.reset(text);
        }

        return text;
    }

    public AuditExceptionForeignReportUtil(String ip, String user, String password, Integer port, String database, String gpSchema) {
        this.ip = ip;
        this.user = user;
        this.password = password;
        this.port = port;
        this.database = database;
        this.gpSchema = gpSchema;
    }

    public Map<String, Object> parseParams(Map<String, Object> params, String cityName, String startTime, String endTime) {
        Connection connection = null;
        try {
            connection = DatabaseUtil.getConnection(ip, DataBaseType.GREENPLUM, user, password, port, database);
            group_1_1_1(connection, params, cityName, startTime, endTime);
            group_1_1_2(connection, params, cityName, startTime, endTime);
            group_1_2_1(connection, params, cityName, startTime, endTime);
            group_1_2_2(connection, params, cityName, startTime, endTime);
            group_1_3_1(connection, params, cityName, startTime, endTime);
            group_1_3_2(connection, params, cityName, startTime, endTime);
            group_1_3_3(connection, params, cityName, startTime, endTime);
            group_1_4_1(connection, params, cityName, startTime, endTime);
            group_1_4_2(connection, params, cityName, startTime, endTime);

            group_2_1_1(connection, params, cityName, startTime, endTime);
            group_2_1_2(connection, params, cityName, startTime, endTime);
            group_2_1_3(connection, params, cityName, startTime, endTime);
            group_2_2_1(connection, params, cityName, startTime, endTime);
            group_2_2_2(connection, params, cityName, startTime, endTime);
            group_2_2_3(connection, params, cityName, startTime, endTime);
            group_2_2_4(connection, params, cityName, startTime, endTime);
            group_2_3_1(connection, params, cityName, startTime, endTime);
            group_2_3_2(connection, params, cityName, startTime, endTime);
            group_2_3_3(connection, params, cityName, startTime, endTime);
            group_2_4_1(connection, params, cityName, startTime, endTime);
            group_2_4_2(connection, params, cityName, startTime, endTime);
            group_2_4_3(connection, params, cityName, startTime, endTime);
            group_2_4_4(connection, params, cityName, startTime, endTime);
            group_2_4_5(connection, params, cityName, startTime, endTime);
            group_2_5_1(connection, params, cityName, startTime, endTime);
            group_2_5_2(connection, params, cityName, startTime, endTime);
            group_2_5_3(connection, params, cityName, startTime, endTime);
            group_2_5_4(connection, params, cityName, startTime, endTime);

            group_2_6_1(connection, params, cityName, startTime, endTime);
            group_2_6_2(connection, params, cityName, startTime, endTime);
            group_2_6_3(connection, params, cityName, startTime, endTime);
            group_2_6_4(connection, params, cityName, startTime, endTime);
            group_2_6_5(connection, params, cityName, startTime, endTime);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return params;
    }

    /**
     * 一、集团通报风险情况 --> （一）客户欠费管理专题 --> 1.集团客户长期高额欠费
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_1_1_1(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {

        //查询最近的数据月
        String sql = "SELECT MAX(date) date FROM " + gpSchema + ".f_audit_group_arrears_list WHERE date BETWEEN " + startTime + " AND " + endTime + " AND city_name ='" + cityName + "'";
        Object dateObj = QueryHelper.selectColumn(connection, sql, "date");
        String flagEndTime=(dateObj == null ? endTime : dateObj.toString());

        String commonWhere = " city_name ='" + cityName + "' AND date = " + flagEndTime;
        String keyPrefix = "p_1_1_1_";

        //段落一
        sql = "SELECT data_month FROM " + gpSchema + ".f_audit_group_arrears_list WHERE vtc='集团客户长期高额欠费' AND " + commonWhere + " limit 1";
        Object data_month = QueryHelper.selectColumn(connection, sql, "data_month");
        if (data_month != null) {
            data_month = data_month.toString().substring(0, 4) + "年" + data_month.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_date", data_month);
        sql = "SELECT COUNT(DISTINCT account_id) c FROM " + gpSchema + ".f_audit_group_arrears_list WHERE vtc='集团客户长期高额欠费' AND " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COALESCE(SUM(arrears_money),0) s FROM " + gpSchema + ".f_audit_group_arrears_list WHERE vtc='集团客户长期高额欠费' AND " + commonWhere;
        Object groupSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", NumberUtil.double2decimal(ObjectUtil.obj2Double(groupSum) / 10000D));

        sql = "SELECT COALESCE(SUM(arrears_money),0) s FROM " + gpSchema + ".f_audit_group_arrears_list WHERE vtc='集团客户长期高额欠费' AND date = " + flagEndTime;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        Double rate = ObjectUtil.calcPercent(groupSum, sum);
        params.put(keyPrefix + "1_3", rate);

        //sql = "SELECT AVG(cr_money/la_money)*100 a FROM " + gpSchema + ".f_audit_group_last_list WHERE " + commonWhere;
        sql = "SELECT ROUND( (SELECT SUM(cr_money) FROM " + gpSchema + ".f_audit_group_last_list WHERE city_name = '" + cityName + "' AND date = " + flagEndTime + ") / (SELECT SUM(arrears_money) FROM " + gpSchema + ".f_audit_group_arrears_list WHERE city_name = '" + cityName + "' AND date = " + DateUtil.turnJavaUtilDateToStrDate(DateUtil.parseAddDate(DateUtil.turnStrDateToJavaUtilDate(flagEndTime, "yyyyMM"), Calendar.MONTH, -1), "yyyyMM") + ")*100,2) a";
        Object cityAvg = QueryHelper.selectColumn(connection, sql, "a");
        rate = NumberUtil.double2decimal(ObjectUtil.obj2Double(cityAvg));
        params.put(keyPrefix + "1_4", rate);
        if (rate > 0) {
            params.put("z_1_1_1_1", "");
        } else {
            params.put("z_1_1_1_2", "");
        }

        //全省占比平均值
        sql = "SELECT ROUND( (SELECT SUM(cr_money) FROM " + gpSchema + ".f_audit_group_last_list WHERE date = " + flagEndTime + ") / (SELECT SUM(arrears_money) FROM " + gpSchema + ".f_audit_group_arrears_list WHERE date = " + DateUtil.turnJavaUtilDateToStrDate(DateUtil.parseAddDate(DateUtil.turnStrDateToJavaUtilDate(flagEndTime, "yyyyMM"), Calendar.MONTH, -1), "yyyyMM") + ")*100,2) a";
        Object avg = QueryHelper.selectColumn(connection, sql, "a");
        params.put(keyPrefix + "1_5", rate > (avg == null ? 0d : ObjectUtil.obj2Double(avg)) ? "高" : "低");

        //段落二

        commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        sql = "SELECT COUNT(DISTINCT gc_id) c FROM " + gpSchema + ".f_audit_group_newopen_list WHERE " + commonWhere;
        count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "2_1", count);

        sql = "SELECT COALESCE(SUM(nobaa_money),0) s FROM " + gpSchema + ".f_audit_group_newopen_list WHERE " + commonWhere;
        sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "2_2", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        if (ObjectUtil.obj2Double(sum) >= 0) {
            params.put("z_1_1_1_3", "");
        } else {
            params.put("z_1_1_1_4", "");
        }

        //查询最近的数据月
        sql = "SELECT MAX(date) date FROM " + gpSchema + ".f_audit_group_selfusetest_list WHERE date BETWEEN " + startTime + " AND " + endTime + " AND city_name ='" + cityName + "'";
        dateObj = QueryHelper.selectColumn(connection, sql, "date");
        flagEndTime=(dateObj == null ? endTime : dateObj.toString());

        commonWhere = " city_name ='" + cityName + "' AND date = " + flagEndTime;
        //
        sql = "SELECT COALESCE(SUM(arrears_money),0) s FROM " + gpSchema + ".f_audit_group_selfusetest_list WHERE " + commonWhere;
        sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "2_3", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COUNT(DISTINCT account_id) c FROM " + gpSchema + ".f_audit_group_selfusetest_list WHERE " + commonWhere;
        count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "2_4", count);

        //表格，欠费金额前5的集团客户
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "3_" + i, "-");
        }


        sql = "SELECT COALESCE(SUM(arrears_money),0) s FROM " + gpSchema + ".f_audit_group_arrears_list WHERE " + commonWhere;
        sum = QueryHelper.selectColumn(connection, sql, "s");

        sql = "SELECT gc_id, gc_name, sum(arrears_money) s FROM " + gpSchema + ".f_audit_group_arrears_list WHERE " + commonWhere + " GROUP BY gc_id,gc_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;

        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "3_" + (i * 4 + 1), maps.get(i).get("gc_id"));
            params.put(keyPrefix + "3_" + (i * 4 + 2), maps.get(i).get("gc_name"));
            params.put(keyPrefix + "3_" + (i * 4 + 3), maps.get(i).get("s"));
            params.put(keyPrefix + "3_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "3_21", NumberUtil.double2String(total));
        params.put(keyPrefix + "3_22", ObjectUtil.calcPercent(total, sum) + "%");

    }

    /**
     * 一、集团通报风险情况 --> （一）客户欠费管理专题 --> 2.个人客户欠费
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_1_1_2(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        //查询最近的数据月
        String sql = "SELECT MAX(date) date FROM " + gpSchema + ".f_audit_group_parl WHERE date BETWEEN " + startTime + " AND " + endTime + " AND city_name ='" + cityName + "'";
        Object dateObj = QueryHelper.selectColumn(connection, sql, "date");
        String flagEndTime= dateObj == null ? endTime : dateObj.toString();

        String commonWhere = " city_name ='" + cityName + "' AND date = " + flagEndTime;
        String keyPrefix = "p_1_1_2_";
        String union = "(SELECT user_id,money,city_name,date FROM " + gpSchema + ".f_audit_group_parl UNION SELECT user_id,arrears_money money,city_name,date FROM " + gpSchema + ".f_audit_group_arrears_list WHERE vtc = '个人客户长期高额欠费') t";

        //段落一
        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + union + " WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);
        sql = "SELECT COALESCE(SUM(money),0) s FROM " + union + " WHERE " + commonWhere;
        Object groupSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", NumberUtil.double2decimal(ObjectUtil.obj2Double(groupSum) / 10000D));
        sql = "SELECT COALESCE(SUM(money),0) s FROM " + union + " WHERE  date BETWEEN " + startTime + " AND " + endTime;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", ObjectUtil.calcPercent(groupSum, sum));

        //表格，欠费金额前5的个人客户：
        for (int i = 1; i <= 17; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT user_id, sum(money) s FROM " + union + " WHERE " + commonWhere + " GROUP BY user_id ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 3 + 1), maps.get(i).get("user_id"));
            params.put(keyPrefix + "2_" + (i * 3 + 2), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 3 + 3), ObjectUtil.calcPercent(maps.get(i).get("s"), groupSum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_16", NumberUtil.double2String(total));
        params.put(keyPrefix + "2_17", ObjectUtil.calcPercent(total, groupSum) + "%");
    }

    /**
     * 一、集团通报风险情况 --> （二）宽带业务管理专题 --> 1.疑似虚假宽带用户
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_1_2_1(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_1_2_1_";
        //段落一
        String sql = "SELECT max(netin_month) endTime ,min(netin_month) startTime FROM " + gpSchema + ".f_audit_group_false_user WHERE " + commonWhere;
        Object netin_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (netin_endTime != null) {
            netin_endTime = netin_endTime.toString().substring(0, 4) + "年" + netin_endTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_endTime", netin_endTime);

        Object netin_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (netin_startTime != null) {
            netin_startTime = netin_startTime.toString().substring(0, 4) + "年" + netin_startTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_startTime", netin_startTime);

        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_group_false_user WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_group_false_user WHERE  date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount));

        //表格，排名前5的办理渠道：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }

        sql = "SELECT hc_id,hc_name,COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_group_false_user WHERE " + commonWhere + " GROUP BY hc_id,hc_name ORDER BY c DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("hc_id"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("hc_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("c"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("c"), count) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c"));
        }
        params.put(keyPrefix + "2_21", total);
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, count) + "%");

    }

    /**
     * 一、集团通报风险情况 --> （二）宽带业务管理专题 --> 2.系统间宽带信息不一致
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_1_2_2(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_1_2_2_";

        //段落一
        String sql = "SELECT max(netin_month) endTime ,min(netin_month) startTime FROM " + gpSchema + ".f_audit_group_broadbandinfo_atypism WHERE " + commonWhere;
        Object netin_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (netin_endTime != null) {
            netin_endTime = netin_endTime.toString().substring(0, 4) + "年" + netin_endTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_endTime", netin_endTime);

        Object netin_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (netin_startTime != null) {
            netin_startTime = netin_startTime.toString().substring(0, 4) + "年" + netin_startTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_startTime", netin_startTime);

        sql = "SELECT COUNT(DISTINCT bu_id) c FROM " + gpSchema + ".f_audit_group_broadbandinfo_atypism WHERE violation_type='综资开通CRM未开通（CRM和综资都有记录）' AND" + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);
        params.put(keyPrefix + "2_1", count);

        sql = "SELECT COUNT(DISTINCT bu_id) c FROM " + gpSchema + ".f_audit_group_broadbandinfo_atypism WHERE violation_type='综资开通CRM未开通（CRM和综资都有记录）' AND date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount));
        params.put(keyPrefix + "2_2", ObjectUtil.calcPercent(count, totalCount) + "%");

        sql = "SELECT COUNT(DISTINCT bu_id) c FROM " + gpSchema + ".f_audit_group_broadbandinfo_atypism WHERE violation_type='CRM开通综资未开通（CRM单边宽带）' AND" + commonWhere;
        count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_3", count);
        params.put(keyPrefix + "2_3", count);

        sql = "SELECT COUNT(DISTINCT bu_id) c FROM " + gpSchema + ".f_audit_group_broadbandinfo_atypism WHERE violation_type='CRM开通综资未开通（CRM单边宽带）' AND date BETWEEN " + startTime + " AND " + endTime;
        totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_4", ObjectUtil.calcPercent(count, totalCount));
        params.put(keyPrefix + "2_4", ObjectUtil.calcPercent(count, totalCount) + "%");
    }

    /**
     * 一、集团通报风险情况 --> （三）集团专线管理专题 --> 1.开通后未及时计费（风险程度：中）
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_1_3_1(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_1_3_1_";

        //段落一
        String sql = "SELECT data_month FROM " + gpSchema + ".f_audit_group_open_uncharging WHERE city_name ='" + cityName + "' AND date = " + endTime + " limit 1";
        Object data_month = QueryHelper.selectColumn(connection, sql, "data_month");
        if (data_month != null) {
            data_month = data_month.toString().substring(0, 4) + "年" + data_month.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_date", data_month);

        sql = "SELECT COUNT(DISTINCT gc_id) c FROM " + gpSchema + ".f_audit_group_open_uncharging WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT gc_id) c FROM " + gpSchema + ".f_audit_group_open_uncharging WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount));

        //表格，开通后未及时计费包含3种业务类型（按集团业务类型分组，按[集团客户标识]去重记录数倒序排列，计算占全市比例）：
        for (int i = 1; i <= 11; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT gbt,COUNT(DISTINCT gc_id) c FROM " + gpSchema + ".f_audit_group_open_uncharging WHERE " + commonWhere + " GROUP BY gbt ORDER BY c DESC LIMIT 3";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 3 + 1), maps.get(i).get("gbt"));
            params.put(keyPrefix + "2_" + (i * 3 + 2), maps.get(i).get("c"));
            params.put(keyPrefix + "2_" + (i * 3 + 3), ObjectUtil.calcPercent(maps.get(i).get("c"), count) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c"));
        }
        params.put(keyPrefix + "2_10", total);
        params.put(keyPrefix + "2_11", ObjectUtil.calcPercent(total, count) + "%");
    }

    /**
     * 一、集团通报风险情况 --> （三）集团专线管理专题 --> 2.正常在用长期不计费（风险程度：中）
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_1_3_2(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        //查询最近的数据月
        String sql = "SELECT MAX(date) date FROM " + gpSchema + ".f_audit_group_nomal_unarreas WHERE date BETWEEN " + startTime + " AND " + endTime + " AND city_name ='" + cityName + "'";
        Object dateObj = QueryHelper.selectColumn(connection, sql, "date");

        String commonWhere = " city_name ='" + cityName + "' AND date = " + (dateObj == null ? endTime : dateObj.toString());
        String keyPrefix = "p_1_3_2_";
        //段落一
        sql = "SELECT data_month FROM " + gpSchema + ".f_audit_group_nomal_unarreas WHERE " + commonWhere + " limit 1";
        Object data_month = QueryHelper.selectColumn(connection, sql, "data_month");
        if (data_month != null) {
            data_month = data_month.toString().substring(0, 4) + "年" + data_month.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_date", data_month);

        sql = "SELECT COUNT(DISTINCT line_id) c FROM " + gpSchema + ".f_audit_group_nomal_unarreas WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT line_id) c FROM " + gpSchema + ".f_audit_group_nomal_unarreas WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount));

        //表格，正常在用长期不计费专线数量排名前5的集团客户：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT gc_id,gc_name,COUNT(line_id) c FROM " + gpSchema + ".f_audit_group_nomal_unarreas WHERE " + commonWhere + " GROUP BY gc_id,gc_name ORDER BY c DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("gc_id"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("gc_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("c"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("c"), count) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c"));
        }
        params.put(keyPrefix + "2_21", total);
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, count) + "%");

    }

    /**
     * 一、集团通报风险情况 --> （三）集团专线管理专题 --> 3.系统间专线信息不一致
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_1_3_3(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        //查询最近的数据月
        String sql = "SELECT MAX(date) date FROM " + gpSchema + ".f_audit_group_info_atypism WHERE date BETWEEN " + startTime + " AND " + endTime + " AND city_name ='" + cityName + "'";
        Object dateObj = QueryHelper.selectColumn(connection, sql, "date");

        String commonWhere = " city_name ='" + cityName + "' AND date = " + (dateObj == null ? endTime : dateObj.toString());
        String keyPrefix = "p_1_3_3_";

        //段落一
        sql = "SELECT data_month FROM " + gpSchema + ".f_audit_group_info_atypism WHERE " + commonWhere + " limit 1";
        Object data_month = QueryHelper.selectColumn(connection, sql, "data_month");
        if (data_month != null) {
            data_month = data_month.toString().substring(0, 4) + "年" + data_month.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_date", data_month);

        sql = "SELECT COUNT(DISTINCT lp_id) c FROM " + gpSchema + ".f_audit_group_info_atypism WHERE (violation_type like 'BOSS侧可用%' or violation_type like 'BOSS侧开通%') AND " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);
        params.put(keyPrefix + "2_1", count);

        sql = "SELECT COUNT(DISTINCT lp_id) c FROM " + gpSchema + ".f_audit_group_info_atypism WHERE (violation_type like 'BOSS侧可用%' or violation_type like 'BOSS侧开通%') AND date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount));
        params.put(keyPrefix + "2_2", ObjectUtil.calcPercent(count, totalCount) + "%");

        sql = "SELECT COUNT(DISTINCT lp_id) c FROM " + gpSchema + ".f_audit_group_info_atypism WHERE (violation_type like '网络侧开通%' or violation_type like '网络侧可用%') AND " + commonWhere;
        count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_3", count);
        params.put(keyPrefix + "2_3", count);

        sql = "SELECT COUNT(DISTINCT lp_id) c FROM " + gpSchema + ".f_audit_group_info_atypism WHERE (violation_type like '网络侧开通%' or violation_type like '网络侧可用%') AND date BETWEEN " + startTime + " AND " + endTime;
        totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_4", ObjectUtil.calcPercent(count, totalCount));
        params.put(keyPrefix + "2_4", ObjectUtil.calcPercent(count, totalCount) + "%");

        sql = "SELECT COUNT(DISTINCT lp_id) c FROM " + gpSchema + ".f_audit_group_info_atypism WHERE violation_type like '%信息不一致%' AND " + commonWhere;
        count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_5", count);
        params.put(keyPrefix + "2_5", count);

        sql = "SELECT COUNT(DISTINCT lp_id) c FROM " + gpSchema + ".f_audit_group_info_atypism WHERE violation_type like '%信息不一致%' AND date BETWEEN " + startTime + " AND " + endTime;
        totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_6", ObjectUtil.calcPercent(count, totalCount));
        params.put(keyPrefix + "2_6", ObjectUtil.calcPercent(count, totalCount) + "%");


    }

    /**
     * 一、集团通报风险情况 --> （四）物联网卡管理专题--> 1.物联网卡用于非适用终端
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_1_4_1(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        //查询最近的数据月
        String sql = "SELECT MAX(date) date FROM " + gpSchema + ".f_audit_group_unfit_teminal WHERE date BETWEEN " + startTime + " AND " + endTime + " AND city_name ='" + cityName + "'";
        Object dateObj = QueryHelper.selectColumn(connection, sql, "date");

        String commonWhere = " city_name ='" + cityName + "' AND date = " + (dateObj == null ? endTime : dateObj.toString());
        String keyPrefix = "p_1_4_1_";

        //段落一
        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_group_unfit_teminal WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_group_unfit_teminal WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount));

        //表格，非适用终端数排名前5的集团客户：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT gc_id,gc_name,COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_group_unfit_teminal WHERE char_length(gc_id)>=10 AND" + commonWhere + " GROUP BY gc_id,gc_name ORDER BY c DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("gc_id"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("gc_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("c"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("c"), count) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c"));
        }
        params.put(keyPrefix + "2_21", total);
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, count) + "%");
    }

    /**
     * 一、集团通报风险情况 --> （四）物联网卡管理专题--> 2.虚增物联网连接数
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_1_4_2(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        //查询最近的数据月
        String sql = "SELECT MAX(date) date FROM " + gpSchema + ".f_audit_group_false_connect WHERE date BETWEEN " + startTime + " AND " + endTime + " AND city_name ='" + cityName + "'";
        Object dateObj = QueryHelper.selectColumn(connection, sql, "date");

        String commonWhere = " city_name ='" + cityName + "' AND date = " + (dateObj == null ? endTime : dateObj.toString());
        String keyPrefix = "p_1_4_2_";

        //段落一
        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_group_false_connect WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_group_false_connect WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount));

        //表格，虚增物联网数排名前5的集团客户：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT gc_id,gc_name,COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_group_false_connect WHERE " + commonWhere + " GROUP BY gc_id,gc_name ORDER BY c DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("gc_id"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("gc_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("c"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("c"), count) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c"));
        }
        params.put(keyPrefix + "2_21", total);
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, count) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （一）客户欠费管理专题--> 1.非红名单集团产品欠费超期未停机
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_1_1(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        //查询最近的数据月
        String sql = "SELECT MAX(date) date FROM " + gpSchema + ".f_audit_arrearage WHERE date BETWEEN " + startTime + " AND " + endTime + " AND city_name ='" + cityName + "'";
        Object dateObj = QueryHelper.selectColumn(connection, sql, "date");

        String commonWhere = " city_name ='" + cityName + "' AND date = " + (dateObj == null ? endTime : dateObj.toString());
        String keyPrefix = "p_2_1_1_";

        //段落一
        sql = "SELECT COUNT(DISTINCT product_code) c FROM " + gpSchema + ".f_audit_arrearage WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT group_code) c FROM " + gpSchema + ".f_audit_arrearage WHERE " + commonWhere;
        count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", count);

        sql = "SELECT COALESCE(SUM(beyond_money),0) s FROM " + gpSchema + ".f_audit_arrearage WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(beyond_money),0) s FROM " + gpSchema + ".f_audit_arrearage WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_4", ObjectUtil.calcPercent(sum, totalSum));

        //表格，欠费金额排名前5的集团客户：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT group_code,group_name,SUM(beyond_money) s FROM " + gpSchema + ".f_audit_arrearage WHERE " + commonWhere + " GROUP BY group_code,group_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("group_code"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("group_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_21", NumberUtil.double2String(ObjectUtil.obj2Double(total)));
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （一）客户欠费管理专题--> 2.集团产品欠费停机超期未销户
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_1_2(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        //查询最近的数据月
        String sql = "SELECT MAX(date) date FROM " + gpSchema + ".f_audit_blocarrear WHERE date BETWEEN " + startTime + " AND " + endTime + " AND city_name ='" + cityName + "'";
        Object dateObj = QueryHelper.selectColumn(connection, sql, "date");

        String commonWhere = " city_name ='" + cityName + "' AND date = " + (dateObj == null ? endTime : dateObj.toString());
        String keyPrefix = "p_2_1_2_";

        //段落一
        sql = "SELECT COUNT(DISTINCT product_code) c FROM " + gpSchema + ".f_audit_blocarrear WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_blocarrear WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_blocarrear WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", ObjectUtil.calcPercent(sum, totalSum));

        //表格，欠费金额排名前5的集团客户：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT group_code,group_name,SUM(money) s FROM " + gpSchema + ".f_audit_blocarrear WHERE " + commonWhere + " GROUP BY group_code,group_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("group_code"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("group_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_21", NumberUtil.double2String(ObjectUtil.obj2Double(total)));
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （一）客户欠费管理专题--> 3.欠费销户集团客户未进入黑名单
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_1_3(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        //查询最近的数据月
        String sql = "SELECT MAX(date) date FROM " + gpSchema + ".f_audit_blocarrear_blacklist WHERE date BETWEEN " + startTime + " AND " + endTime + " AND city_name ='" + cityName + "'";
        Object dateObj = QueryHelper.selectColumn(connection, sql, "date");

        String commonWhere = " city_name ='" + cityName + "' AND date = " + (dateObj == null ? endTime : dateObj.toString());

        String keyPrefix = "p_2_1_3_";
        //段落一
        sql = "SELECT COUNT(DISTINCT product_code) c FROM " + gpSchema + ".f_audit_blocarrear_blacklist WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", count);

        sql = "SELECT COUNT(DISTINCT group_code) c FROM " + gpSchema + ".f_audit_blocarrear_blacklist WHERE " + commonWhere;
        count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_blocarrear_blacklist WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_blocarrear_blacklist WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_4", ObjectUtil.calcPercent(sum, totalSum));

        //表格，未及时进入黑名单涉及欠费金额排名前5的集团客户：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT group_code,group_name,SUM(money) s FROM " + gpSchema + ".f_audit_blocarrear_blacklist WHERE " + commonWhere + " GROUP BY group_code,group_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("group_code"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("group_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_21", NumberUtil.double2String(ObjectUtil.obj2Double(total)));
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （二）营销管理专题 --> 1.集团代付产品违规转售
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_2_1(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_2_1_";
        //段落一
        String sql = "SELECT COUNT(DISTINCT group_code) c FROM " + gpSchema + ".f_audit_pay_group WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COALESCE(SUM(pay_money),0) s FROM " + gpSchema + ".f_audit_pay_group WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(pay_money),0) s FROM " + gpSchema + ".f_audit_pay_group WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", ObjectUtil.calcPercent(sum, totalSum));

        //表格，未及时进入黑名单涉及欠费金额排名前5的集团客户：
        for (int i = 1; i <= 28; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT group_code,group_name,SUM(pay_money) s,SUM(pay_cnt) sc FROM " + gpSchema + ".f_audit_pay_group WHERE " + commonWhere + " GROUP BY group_code,group_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        Double totalCount = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 5 + 1), maps.get(i).get("group_code"));
            params.put(keyPrefix + "2_" + (i * 5 + 2), maps.get(i).get("group_name"));
            params.put(keyPrefix + "2_" + (i * 5 + 3), maps.get(i).get("sc"));
            params.put(keyPrefix + "2_" + (i * 5 + 4), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 5 + 5), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
            totalCount += ObjectUtil.obj2Double(maps.get(i).get("sc"));
        }
        params.put(keyPrefix + "2_26", NumberUtil.double2String(ObjectUtil.obj2Double(totalCount)));
        params.put(keyPrefix + "2_27", NumberUtil.double2String(ObjectUtil.obj2Double(total)));
        params.put(keyPrefix + "2_28", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （二）营销管理专题 --> 2.单用户单月高额积分异常变动
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_2_2(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_2_2_";
        //段落一
        String sql = "SELECT COUNT(DISTINCT msisdn) c FROM " + gpSchema + ".f_audit_integral_change WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT SUM(change_limit) s FROM " + gpSchema + ".f_audit_integral_change WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", sum);

        sql = "SELECT SUM(change_limit) s FROM " + gpSchema + ".f_audit_integral_change WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", ObjectUtil.calcPercent(sum, totalSum));

        //表格，积分变动排名前5的用户：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT msisdn,user_code,SUM(change_limit) s FROM " + gpSchema + ".f_audit_integral_change WHERE " + commonWhere + " GROUP BY msisdn,user_code ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("msisdn"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("user_code"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_21", NumberUtil.double2String(ObjectUtil.obj2Double(total)));
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （二）营销管理专题 --> 3.通过调账方式违规为黑名单客户办理优惠方案
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_2_3(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_2_3_";
        //段落一
        String sql = "SELECT COALESCE(SUM(change_money),0) s FROM " + gpSchema + ".f_audit_give_resource WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_1", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(change_money),0) s FROM " + gpSchema + ".f_audit_give_resource WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(sum, totalSum));


        //表格，违规办理优惠方案排名前5的黑名单用户：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT group_code,SUM(change_money) s FROM " + gpSchema + ".f_audit_give_resource WHERE " + commonWhere + " GROUP BY group_code ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("group_code"));
            //params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("product_code"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_21", NumberUtil.double2String(total));
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （二）营销管理专题 --> 4.承诺低消类营销方案未互斥
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_2_4(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_2_4_";
        //段落一
        String sql = "SELECT COUNT(DISTINCT product_code) c FROM " + gpSchema + ".f_audit_low_sale_mutex WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT product_code) c FROM " + gpSchema + ".f_audit_low_sale_mutex WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount));


        //表格，违规办理优惠方案排名前5的黑名单用户：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT product_code,product_name,SUM(handel_cnt) s FROM " + gpSchema + ".f_audit_low_sale_mutex WHERE " + commonWhere + " GROUP BY product_code,product_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        sql = "SELECT SUM(handel_cnt) s FROM " + gpSchema + ".f_audit_low_sale_mutex WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "S");

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("product_code"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("product_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("s"), totalSum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_21", total);
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, totalSum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （三）合同管理专题 --> 1.合同拆分规避分级审批
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_3_1(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_3_1_";
        //段落一
        String sql = "SELECT max(audit_time) endTime ,min(audit_time) startTime FROM " + gpSchema + ".f_audit_level_audit WHERE " + commonWhere;
        Object hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            hz_endTime = hz_endTime.toString().substring(0, 4) + "年" + hz_endTime.toString().substring(5, 7) + "月";
        }
        params.put(keyPrefix + "1_endTime", hz_endTime);

        Object hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            hz_startTime = hz_startTime.toString().substring(0, 4) + "年" + hz_startTime.toString().substring(5, 7) + "月";
        }
        params.put(keyPrefix + "1_startTime", hz_startTime);

        sql = "SELECT COUNT(1) c FROM " + gpSchema + ".f_audit_level_audit WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COALESCE(SUM(ltail),0) s FROM " + gpSchema + ".f_audit_level_audit WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(ltail),0) s FROM " + gpSchema + ".f_audit_level_audit WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", ObjectUtil.calcPercent(sum, totalSum));


        //表格，同一项目疑似拆分合同金额排名前5的合同：
        for (int i = 1; i <= 27; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT contract_num,contract_name,lop_name,SUM(ltail) s FROM " + gpSchema + ".f_audit_level_audit WHERE " + commonWhere + " GROUP BY contract_num,contract_name,lop_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 5 + 1), maps.get(i).get("contract_num"));
            params.put(keyPrefix + "2_" + (i * 5 + 2), maps.get(i).get("contract_name"));
            params.put(keyPrefix + "2_" + (i * 5 + 3), maps.get(i).get("lop_name"));
            params.put(keyPrefix + "2_" + (i * 5 + 4), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 5 + 5), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_26", NumberUtil.double2String(total));
        params.put(keyPrefix + "2_27", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （三）合同管理专题 --> 2.合同拆分规避采购流程
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_3_2(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_3_2_";
        //段落一
        String sql = "SELECT max(audit_time) endTime ,min(audit_time) startTime FROM " + gpSchema + ".f_audit_proc_process WHERE " + commonWhere;
        Object hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            hz_endTime = hz_endTime.toString().substring(0, 4) + "年" + hz_endTime.toString().substring(5, 7) + "月";
        }
        params.put(keyPrefix + "1_endTime", hz_endTime);

        Object hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            hz_startTime = hz_startTime.toString().substring(0, 4) + "年" + hz_startTime.toString().substring(5, 7) + "月";
        }
        params.put(keyPrefix + "1_startTime", hz_startTime);

        sql = "SELECT COUNT(1) c FROM " + gpSchema + ".f_audit_proc_process WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT lop_name) c FROM " + gpSchema + ".f_audit_proc_process WHERE  " + commonWhere;
        count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", count);

        sql = "SELECT COALESCE(SUM(ltail),0) s FROM " + gpSchema + ".f_audit_proc_process WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(ltail),0) s FROM " + gpSchema + ".f_audit_proc_process WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_4", ObjectUtil.calcPercent(sum, totalSum));


        //表格，同一项目疑似拆分合同金额排名前5的合同：
        for (int i = 1; i <= 27; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT contract_num,contract_name,lop_name,SUM(ltail) s FROM " + gpSchema + ".f_audit_proc_process WHERE " + commonWhere + " GROUP BY contract_num,contract_name,lop_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 5 + 1), maps.get(i).get("contract_num"));
            params.put(keyPrefix + "2_" + (i * 5 + 2), maps.get(i).get("contract_name"));
            params.put(keyPrefix + "2_" + (i * 5 + 3), maps.get(i).get("lop_name"));
            params.put(keyPrefix + "2_" + (i * 5 + 4), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 5 + 5), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_26", NumberUtil.double2String(total));
        params.put(keyPrefix + "2_27", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （三）合同管理专题 --> 3.超五万元项目未签署合同
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_3_3(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_3_3_";
        //段落一
        String sql = "SELECT create_time endTime FROM " + gpSchema + ".f_audit_unsign_contract WHERE  city_name ='" + cityName + "' AND date = " + endTime;
        Object hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            String[] split = hz_endTime.toString().split("-");
            hz_endTime = split[0] + "年" + split[1] + "月";
        }
        params.put(keyPrefix + "1_endTime", hz_endTime);

        sql = "SELECT create_time startTime FROM " + gpSchema + ".f_audit_unsign_contract WHERE  city_name ='" + cityName + "' AND date = " + startTime;
        Object hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            String[] split = hz_startTime.toString().split("-");
            hz_startTime = split[0] + "年" + split[1] + "月";
        }
        params.put(keyPrefix + "1_startTime", hz_startTime);

        sql = "SELECT COUNT(1) c FROM " + gpSchema + ".f_audit_unsign_contract WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_unsign_contract WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_unsign_contract WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", ObjectUtil.calcPercent(sum, totalSum));


        //表格，超五万元项目未签合同金额排名前5的报账记录：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT bill_num,bill_type,SUM(money) s FROM " + gpSchema + ".f_audit_unsign_contract WHERE " + commonWhere + " GROUP BY bill_num,bill_type ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("bill_num"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("bill_type"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_21", NumberUtil.double2String(total));
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, sum) + "%");
    }


    /**
     * 二、省内通报风险情况 --> （四）酬金管理专题 --> 1.酬金重复结算
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_4_1(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_4_1_";
        //段落一
        String sql = "SELECT max(date_hz) endTime ,min(date_hz) startTime FROM " + gpSchema + ".f_audit_mony_double_settlement WHERE " + commonWhere;
        Object hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            hz_endTime = hz_endTime.toString().substring(0, 4) + "年" + hz_endTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_endTime", hz_endTime);

        Object hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            hz_startTime = hz_startTime.toString().substring(0, 4) + "年" + hz_startTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_startTime", hz_startTime);

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_mony_double_settlement WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_1", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_mony_double_settlement WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(sum, totalSum));


        //表格，酬金金额排名前5的办理渠道：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT channel_code,channel_name,SUM(money) s FROM " + gpSchema + ".f_audit_mony_double_settlement WHERE " + commonWhere + " GROUP BY channel_code,channel_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("channel_code"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("channel_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_21", NumberUtil.double2String(total));
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （四）酬金管理专题 --> 2.酬金异常支付
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_4_2(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_4_2_";
        //段落一
        String sql = "SELECT max(date_hz) endTime ,min(date_hz) startTime FROM " + gpSchema + ".f_audit_mony_exception_pay WHERE " + commonWhere;
        Object hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            hz_endTime = hz_endTime.toString().substring(0, 4) + "年" + hz_endTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_endTime", hz_endTime);

        Object hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            hz_startTime = hz_startTime.toString().substring(0, 4) + "年" + hz_startTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_startTime", hz_startTime);

        //sql = "SELECT COUNT(DISTINCT bu_id) c FROM " + gpSchema + ".f_audit_group_broadbandinfo_atypism WHERE violation_type='综资开通CRM未开通（CRM和综资都有记录）' AND" + commonWhere;

        sql = "SELECT COUNT(DISTINCT channel_code) c FROM " + gpSchema + ".f_audit_mony_exception_pay WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COALESCE(SUM(money_total),0) s FROM " + gpSchema + ".f_audit_mony_exception_pay WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(money_total),0) s FROM " + gpSchema + ".f_audit_mony_exception_pay WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", ObjectUtil.calcPercent(sum, totalSum));


        //表格，酬金金额排名前5的办理渠道：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT channel_code,channel_name,SUM(money_total) s FROM " + gpSchema + ".f_audit_mony_exception_pay WHERE " + commonWhere + " GROUP BY channel_code,channel_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("channel_code"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("channel_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_21", NumberUtil.double2String(total));
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （四）酬金管理专题 --> 3.门店补贴超标准结算
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_4_3(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_4_3_";
        //段落一

        String sql = "SELECT max(date_hz) endTime ,min(date_hz) startTime FROM " + gpSchema + ".f_audit_shopsubsidy WHERE " + commonWhere;
        Object hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            hz_endTime = hz_endTime.toString().substring(0, 4) + "年" + hz_endTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_endTime", hz_endTime);

        Object hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            hz_startTime = hz_startTime.toString().substring(0, 4) + "年" + hz_startTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_startTime", hz_startTime);

        sql = "SELECT COALESCE(SUM(sbvlm),0) s FROM " + gpSchema + ".f_audit_shopsubsidy WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_1", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(sbvlm),0) s FROM " + gpSchema + ".f_audit_shopsubsidy WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(sum, totalSum));


        //表格，酬金金额排名前5的办理渠道：
        for (int i = 1; i <= 32; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT channel_code,channel_name,MAX ( bvl ) bvl,MAX ( base_value ) base_value,SUM ( sbvlm ) s FROM s_cxsj.f_audit_shopsubsidy WHERE " + commonWhere + " GROUP BY channel_code,channel_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 6 + 1), maps.get(i).get("channel_code"));
            params.put(keyPrefix + "2_" + (i * 6 + 2), maps.get(i).get("channel_name"));
            params.put(keyPrefix + "2_" + (i * 6 + 3), maps.get(i).get("bvl"));
            params.put(keyPrefix + "2_" + (i * 6 + 4), maps.get(i).get("base_value"));
            params.put(keyPrefix + "2_" + (i * 6 + 5), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 6 + 6), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_31", NumberUtil.double2String(total));
        params.put(keyPrefix + "2_32", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （四）酬金管理专题 --> 4.酬金调整异常操作
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_4_4(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_4_4_";
        //段落一

        String sql = "SELECT max(date_hz) endTime ,min(date_hz) startTime FROM " + gpSchema + ".f_audit_num_null WHERE " + commonWhere;
        Object hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            hz_endTime = hz_endTime.toString().substring(0, 4) + "年" + hz_endTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_endTime", hz_endTime);

        Object hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            hz_startTime = hz_startTime.toString().substring(0, 4) + "年" + hz_startTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_startTime", hz_startTime);

        sql = "SELECT business_name FROM " + gpSchema + ".f_audit_num_null WHERE  " + commonWhere + "GROUP BY 1 order by sum(money) desc limit 2";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
        String businessNames = maps.stream().map(map -> String.valueOf(map.get("business_name"))).collect(Collectors.joining(","));
        params.put(keyPrefix + "1_1", businessNames);

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_num_null WHERE  " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_num_null WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", ObjectUtil.calcPercent(sum, totalSum));


        //表格，酬金金额排名前5的办理渠道(group by 渠道编码,业务名称）：
        for (int i = 1; i <= 32; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT channel_code,channel_name,business_code,business_name,SUM(money) s FROM " + gpSchema + ".f_audit_num_null WHERE " + commonWhere + " GROUP BY channel_code,channel_name,business_code,business_name ORDER BY s DESC LIMIT 5";
        maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 6 + 1), maps.get(i).get("channel_code"));
            params.put(keyPrefix + "2_" + (i * 6 + 2), maps.get(i).get("channel_name"));
            params.put(keyPrefix + "2_" + (i * 6 + 3), maps.get(i).get("business_code"));
            params.put(keyPrefix + "2_" + (i * 6 + 4), maps.get(i).get("business_name"));
            params.put(keyPrefix + "2_" + (i * 6 + 5), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 6 + 6), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_31", NumberUtil.double2String(total));
        params.put(keyPrefix + "2_32", ObjectUtil.calcPercent(total, sum) + "%");

        //段落二，达标值异常调低
        sql = "SELECT max(snapshot_month2) endTime ,min(snapshot_month2) startTime FROM " + gpSchema + ".f_audit_value_lower WHERE " + commonWhere;
        hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            hz_endTime = hz_endTime.toString().substring(0, 4) + "年" + hz_endTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "3_startTime", hz_endTime);

        hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            hz_startTime = hz_startTime.toString().substring(0, 4) + "年" + hz_startTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "3_endTime", hz_startTime);

        sql = "SELECT count(1) tc FROM " + gpSchema + ".f_audit_value_lower WHERE  " + commonWhere;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "tc");
        params.put(keyPrefix + "3_1", totalCount);

        sql = "SELECT count(DISTINCT qrbsi) c FROM " + gpSchema + ".f_audit_value_lower WHERE  " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "3_2", count);

        //段落二表格，达标值异常调低记录数排名前5的办理渠道：
        for (int i = 1; i <= 27; i++) {
            params.put(keyPrefix + "4_" + i, "-");
        }
        sql = "SELECT channel_code,COUNT(DISTINCT qrbsi) c,COUNT(1) tc FROM " + gpSchema + ".f_audit_value_lower WHERE " + commonWhere + " GROUP BY channel_code ORDER BY tc DESC LIMIT 5";
        maps = QueryHelper.selectMap(connection, sql);
        total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "4_" + (i * 5 + 1), maps.get(i).get("channel_code"));
            params.put(keyPrefix + "4_" + (i * 5 + 3), maps.get(i).get("c"));
            params.put(keyPrefix + "4_" + (i * 5 + 4), maps.get(i).get("tc"));
            params.put(keyPrefix + "4_" + (i * 5 + 5), ObjectUtil.calcPercent(maps.get(i).get("tc"), count) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("tc"));
        }
        params.put(keyPrefix + "4_26", total);
        params.put(keyPrefix + "4_27", ObjectUtil.calcPercent(total, count) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （四）酬金管理专题 --> 5.违规向代理商自用集团业务结算酬金
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_4_5(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_4_5_";
        //段落一

        String sql = "SELECT max(date_hz) endTime ,min(date_hz) startTime FROM " + gpSchema + ".f_audit_violation_selfuse WHERE " + commonWhere;
        Object hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            hz_endTime = hz_endTime.toString().substring(0, 4) + "年" + hz_endTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_endTime", hz_endTime);

        Object hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            hz_startTime = hz_startTime.toString().substring(0, 4) + "年" + hz_startTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_startTime", hz_startTime);

        sql = "SELECT COUNT(DISTINCT agent_code) c FROM " + gpSchema + ".f_audit_violation_selfuse WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_violation_selfuse WHERE " + commonWhere;
        Object sum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_2", NumberUtil.double2decimal(ObjectUtil.obj2Double(sum) / 10000D));

        sql = "SELECT COALESCE(SUM(money),0) s FROM " + gpSchema + ".f_audit_violation_selfuse WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalSum = QueryHelper.selectColumn(connection, sql, "s");
        params.put(keyPrefix + "1_3", ObjectUtil.calcPercent(sum, totalSum));


        //表格，酬金金额排名前5的代理商：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT agent_code,agent_name,SUM(money) s FROM " + gpSchema + ".f_audit_violation_selfuse WHERE " + commonWhere + " GROUP BY agent_code,agent_name ORDER BY s DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("agent_code"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("agent_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("s"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("s"), sum) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("s"));
        }
        params.put(keyPrefix + "2_21", NumberUtil.double2String(total));
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, sum) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （五）家庭业务管理专题 --> 1.装维人员领用设备长期未送装
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_5_1(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_5_1_";
        //段落一

        String sql = "SELECT use_time endTime FROM " + gpSchema + ".f_audit_equipmentuninstalled WHERE  city_name ='" + cityName + "' AND date = " + endTime;
        Object hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            String[] split = hz_endTime.toString().split("-");
            hz_endTime = split[0] + "年" + split[1] + "月";
        }
        params.put(keyPrefix + "1_endTime", hz_endTime);

        sql = "SELECT use_time startTime FROM " + gpSchema + ".f_audit_equipmentuninstalled WHERE  city_name ='" + cityName + "' AND date = " + startTime;
        Object hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            String[] split = hz_startTime.toString().split("-");
            hz_startTime = split[0] + "年" + split[1] + "月";
        }
        params.put(keyPrefix + "1_startTime", hz_startTime);


        sql = "SELECT COUNT(tsc) c FROM " + gpSchema + ".f_audit_equipmentuninstalled WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(tsc) c FROM " + gpSchema + ".f_audit_equipmentuninstalled WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount));


        //表格，单名装维人员设备领用数排名前5：
        for (int i = 1; i <= 17; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT imp_id,COUNT(tsc) c FROM " + gpSchema + ".f_audit_equipmentuninstalled WHERE " + commonWhere + " GROUP BY imp_id ORDER BY c DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 3 + 1), maps.get(i).get("imp_id"));
            params.put(keyPrefix + "2_" + (i * 3 + 2), maps.get(i).get("c"));
            params.put(keyPrefix + "2_" + (i * 3 + 3), ObjectUtil.calcPercent(maps.get(i).get("c"), count) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c"));
        }
        params.put(keyPrefix + "2_16", total);
        params.put(keyPrefix + "2_17", ObjectUtil.calcPercent(total, count) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （五）家庭业务管理专题 --> 2.已销户宽带长期未拆机或占用端口
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_5_2(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_5_2_";
        //段落一

        String sql = "SELECT max(state_time) endTime ,min(state_time) startTime FROM " + gpSchema + ".f_audit_unmachine_broadband WHERE " + commonWhere;
        Object hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            hz_endTime = hz_endTime.toString().substring(0, 4) + "年" + hz_endTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_endTime", hz_endTime);

        Object hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            hz_startTime = hz_startTime.toString().substring(0, 4) + "年" + hz_startTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_startTime", hz_startTime);

        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_unmachine_broadband WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_unmachine_broadband WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount));


        //表格，已销户仍占用端口数量排名前5的社区（5级地址）：
        for (int i = 1; i <= 17; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT address5_name,COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_unmachine_broadband WHERE " + commonWhere + " GROUP BY address5_name ORDER BY c DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 3 + 1), maps.get(i).get("address5_name"));
            params.put(keyPrefix + "2_" + (i * 3 + 2), maps.get(i).get("c"));
            params.put(keyPrefix + "2_" + (i * 3 + 3), ObjectUtil.calcPercent(maps.get(i).get("c"), count) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c"));
        }
        params.put(keyPrefix + "2_16", total);
        params.put(keyPrefix + "2_17", ObjectUtil.calcPercent(total, count) + "%");
    }

    /**
     * 二、省内通报风险情况 --> （五）家庭业务管理专题 --> 3.已报废终端仍在网使用
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_5_3(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        //查询最近的数据月
        String sql = "SELECT MAX(date) date FROM " + gpSchema + ".f_audit_terminal_broadband WHERE date BETWEEN " + startTime + " AND " + endTime + " AND city_name ='" + cityName + "'";
        Object dateObj = QueryHelper.selectColumn(connection, sql, "date");

        String commonWhere = " city_name ='" + cityName + "' AND date = " + (dateObj == null ? endTime : dateObj.toString());
        String keyPrefix = "p_2_5_3_";
        //段落一
        sql = "SELECT COUNT(DISTINCT imei) c FROM " + gpSchema + ".f_audit_terminal_broadband WHERE " + commonWhere;
        Object onuCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_3", onuCount);

        sql = "SELECT COUNT(DISTINCT imei) c FROM " + gpSchema + ".f_audit_terminal_tv WHERE " + commonWhere;
        Object tvCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_4", tvCount);

        Double count = ObjectUtil.obj2Double(onuCount) + ObjectUtil.obj2Double(tvCount);

        params.put(keyPrefix + "1_1", count);

        sql = "SELECT count(1) c FROM ( " +
                "SELECT DISTINCT imei FROM " + gpSchema + ".f_audit_terminal_tv WHERE date BETWEEN " + startTime + " AND " + endTime +
                " UNION " +
                "SELECT imei FROM " + gpSchema + ".f_audit_terminal_broadband WHERE date BETWEEN " + startTime + " AND " + endTime +
                " ) t";
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, onuCount));
    }

    /**
     * 二、省内通报风险情况 --> （五）家庭业务管理专题 --> 4.已销户或离网家庭用户终端未回收
     *
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     * @throws SQLException
     */
    public void group_2_5_4(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_5_4_";
        //段落一

        String sql = "SELECT max(state_time) endTime ,min(state_time) startTime FROM " + gpSchema + ".f_audit_unrecovery_terminal WHERE " + commonWhere;
        Object hz_endTime = QueryHelper.selectColumn(connection, sql, "endTime");
        if (hz_endTime != null) {
            hz_endTime = hz_endTime.toString().substring(0, 4) + "年" + hz_endTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_endTime", hz_endTime);

        Object hz_startTime = QueryHelper.selectColumn(connection, sql, "startTime");
        if (hz_startTime != null) {
            hz_startTime = hz_startTime.toString().substring(0, 4) + "年" + hz_startTime.toString().substring(4, 6) + "月";
        }
        params.put(keyPrefix + "1_startTime", hz_startTime);

        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_unrecovery_terminal WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_unrecovery_terminal WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount));


        //表格，已销户仍占用端口数量排名前5的社区（5级地址）：
        for (int i = 1; i <= 17; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT channel_name,COUNT(DISTINCT user_id) c FROM " + gpSchema + ".f_audit_unrecovery_terminal WHERE " + commonWhere + " GROUP BY channel_name ORDER BY c DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 3 + 1), maps.get(i).get("channel_name"));
            params.put(keyPrefix + "2_" + (i * 3 + 2), maps.get(i).get("c"));
            params.put(keyPrefix + "2_" + (i * 3 + 3), ObjectUtil.calcPercent(maps.get(i).get("c"), count) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c"));
        }
        params.put(keyPrefix + "2_16", total);
        params.put(keyPrefix + "2_17", ObjectUtil.calcPercent(total, count) + "%");
    }

    /**
     * 二、省内通报风险情况 -->（六）IDC、ICT业务管理专题 --> 1.疑似转销大带宽业务资源给TOP55客户
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     */
    private void group_2_6_1(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_6_1_";
        //段落一
        String hz_endTime = endTime.substring(0, 4) + "年" + endTime.substring(4, 6) + "月";
        params.put(keyPrefix + "1_endTime", hz_endTime);

        String hz_startTime = startTime.substring(0, 4) + "年" + startTime.substring(4, 6) + "月";
        params.put(keyPrefix + "1_startTime", hz_startTime);

        String sql = "SELECT COUNT(DISTINCT cust_nam) c FROM " + gpSchema + ".f_resell_largebd_to_top55 WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql = "SELECT COUNT(DISTINCT ip) c FROM " + gpSchema + ".f_resell_largebd_to_top55 WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", totalCount);


        //表格，已销户仍占用端口数量排名前5的社区（5级地址）：
        for (int i = 1; i <= 33; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "select t1.CUST_NAM,t1.pro_code_con,t2.pro_name,sum(t2.woff),t1.IP_con from " +
                "(select CUST_NAM,count(distinct product_code) pro_code_con,gcust_id,count(distinct IP) IP_con " +
                "from  "+ gpSchema +".f_resell_largebd_to_top55 " +
                "where " + commonWhere + " group by CUST_NAM,gcust_id)t1 " +
                "left join " +
                "(select substr(group_id,4) ID,pro_name,sum(woff_amt) woff from "+ gpSchema +".f_corp_acct_det_month " +
                "where stat_mo between "+ startTime +" and "+ endTime +
                " and pro_name in('IDC带宽出租','互联网专线') " +
                "group by ID,pro_name)t2 " +
                "on t1.gcust_id = t2.ID " +
                "group by t1.CUST_NAM,t1.pro_code_con,t2.pro_name,t1.IP_con " +
                "order by t1.IP_con desc limit 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        Double totalSum = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 6 + 1), maps.get(i).get("cust_nam"));
            params.put(keyPrefix + "2_" + (i * 6 + 2), maps.get(i).get("pro_code_con"));
            params.put(keyPrefix + "2_" + (i * 6 + 3), maps.get(i).get("pro_name"));
            params.put(keyPrefix + "2_" + (i * 6 + 4), maps.get(i).get("sum"));
            params.put(keyPrefix + "2_" + (i * 6 + 5), maps.get(i).get("IP_con"));
            params.put(keyPrefix + "2_" + (i * 6 + 6), ObjectUtil.calcPercent(maps.get(i).get("IP_con"), totalCount) + "%");
            totalSum += ObjectUtil.obj2Double(maps.get(i).get("sum") != null ? maps.get(i).get("sum") : 0D);
            total += ObjectUtil.obj2Double(maps.get(i).get("IP_con") != null ? maps.get(i).get("IP_con") : 0D);
        }
        DecimalFormat df = new DecimalFormat("0.00");
        params.put(keyPrefix + "2_31", df.format(totalSum));
        params.put(keyPrefix + "2_32", total);
        params.put(keyPrefix + "2_33", ObjectUtil.calcPercent(total, totalCount) + "%");
    }

    /**
     *  二、省内通报风险情况 -->（六）IDC、ICT业务管理专题 --> 2.ICT项目未按规定分摊收入
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     */
    private void group_2_6_2(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_6_2_";
        //段落一
        String hz_endTime = endTime.substring(0, 4) + "年" + endTime.substring(4, 6) + "月";
        params.put(keyPrefix + "1_endTime", hz_endTime);

        String hz_startTime = startTime.substring(0, 4) + "年" + startTime.substring(4, 6) + "月";
        params.put(keyPrefix + "1_startTime", hz_startTime);

        String sql = "SELECT COUNT(DISTINCT amt_cz) c FROM " + gpSchema + ".f_idc_unallocated_income WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        //全省总金额
        sql = "SELECT COUNT(DISTINCT amt_cz) c FROM " + gpSchema + ".f_idc_unallocated_income WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount) + "%");


        //表格，已销户仍占用端口数量排名前5的社区（5级地址）：
        for (int i = 1; i <= 23; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT group_name,bill_name,sum(amt_cz) c_amt FROM " + gpSchema + ".f_idc_unallocated_income WHERE " + commonWhere + "  GROUP BY group_name,bill_name ORDER BY c_amt DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("group_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("bill_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("c_amt"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("c_amt"), totalCount) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c_amt"));
        }
        DecimalFormat df = new DecimalFormat("0.00");
        params.put(keyPrefix + "2_22", df.format(total));
        params.put(keyPrefix + "2_23", ObjectUtil.calcPercent(total, totalCount) + "%");
    }

    /**
     *  二、省内通报风险情况 -->（六）IDC、ICT业务管理专题 --> 3.IDC业务资费低于集团价格底线
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     */
    private void group_2_6_3(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_6_3_";
        //段落一
        String hz_endTime = endTime.substring(0, 4) + "年" + endTime.substring(4, 6) + "月";
        params.put(keyPrefix + "1_endTime", hz_endTime);

        String hz_startTime = startTime.substring(0, 4) + "年" + startTime.substring(4, 6) + "月";
        params.put(keyPrefix + "1_startTime", hz_startTime);

        String sql = "SELECT sum(lower_bottomprice) c FROM " + gpSchema + ".f_idc_lower_bottomprice WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        //全省总金额
        sql = "SELECT sum(lower_bottomprice) c FROM " + gpSchema + ".f_idc_lower_bottomprice WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount) + "%");


        //表格，已销户仍占用端口数量排名前5的社区（5级地址）：
        for (int i = 1; i <= 27; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT room_name,monthly_rent,bottomprice,sum(lower_bottomprice) c_lower FROM " + gpSchema + ".f_idc_lower_bottomprice WHERE " + commonWhere + "  GROUP BY room_name,monthly_rent,bottomprice ORDER BY c_lower DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 5 + 1), maps.get(i).get("room_name"));
            params.put(keyPrefix + "2_" + (i * 5 + 2), maps.get(i).get("monthly_rent"));
            params.put(keyPrefix + "2_" + (i * 5 + 3), maps.get(i).get("bottomprice"));
            params.put(keyPrefix + "2_" + (i * 5 + 4), maps.get(i).get("c_lower"));
            params.put(keyPrefix + "2_" + (i * 5 + 5), ObjectUtil.calcPercent(maps.get(i).get("c_lower"), totalCount) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c_lower"));
        }
        DecimalFormat df = new DecimalFormat("0.00");
        params.put(keyPrefix + "2_26", df.format(total));
        params.put(keyPrefix + "2_27", ObjectUtil.calcPercent(total, totalCount) + "%");
    }

    /**
     *  二、省内通报风险情况 -->（六）IDC、ICT业务管理专题 --> 4.跨集团资金异常销账
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     */
    private void group_2_6_4(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_6_4_";
        //段落一
        String hz_endTime = endTime.substring(0, 4) + "年" + endTime.substring(4, 6) + "月";
        params.put(keyPrefix + "1_endTime", hz_endTime);

        String hz_startTime = startTime.substring(0, 4) + "年" + startTime.substring(4, 6) + "月";
        params.put(keyPrefix + "1_startTime", hz_startTime);

        String sql = "SELECT sum(AMT) c FROM " + gpSchema + ".f_abnormal_writeoff_crossgroup_funds WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        sql += " AND bisn_type = 'IDC' ";
        Object IdcCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_3", IdcCount);

        //全省总金额
        sql = "SELECT sum(AMT) c FROM " + gpSchema + ".f_abnormal_writeoff_crossgroup_funds WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_2", ObjectUtil.calcPercent(count, totalCount) + "%");



        //表格，已销户仍占用端口数量排名前5的社区（5级地址）：
        for (int i = 1; i <= 27; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT group_name,paym_acct_name,bisn_type,sum(AMT) c_AMT from " + gpSchema + ".f_abnormal_writeoff_crossgroup_funds WHERE " + commonWhere + "  GROUP BY group_name,paym_acct_name,bisn_type ORDER BY c_AMT DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 5 + 1), maps.get(i).get("group_name"));
            params.put(keyPrefix + "2_" + (i * 5 + 2), maps.get(i).get("paym_acct_name"));
            params.put(keyPrefix + "2_" + (i * 5 + 3), maps.get(i).get("bisn_type"));
            params.put(keyPrefix + "2_" + (i * 5 + 4), maps.get(i).get("c_AMT"));
            params.put(keyPrefix + "2_" + (i * 5 + 5), ObjectUtil.calcPercent(maps.get(i).get("c_AMT"), totalCount) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c_AMT"));
        }
        DecimalFormat df = new DecimalFormat("0.00");
        params.put(keyPrefix + "2_26", df.format(total));
        params.put(keyPrefix + "2_27", ObjectUtil.calcPercent(total, totalCount) + "%");
    }

    /**
     *  二、省内通报风险情况 -->（六）IDC、ICT业务管理专题 --> 5.IDC资源开通后未及时计费
     * @param connection
     * @param params
     * @param cityName
     * @param startTime
     * @param endTime
     */
    private void group_2_6_5(Connection connection, Map<String, Object> params, String cityName, String startTime, String endTime) throws SQLException {
        String commonWhere = " city_name ='" + cityName + "' AND date BETWEEN " + startTime + " AND " + endTime;
        String keyPrefix = "p_2_6_5_";
        //段落一
        String hz_endTime = endTime.substring(0, 4) + "年" + endTime.substring(4, 6) + "月";
        params.put(keyPrefix + "1_endTime", hz_endTime);

        String hz_startTime = startTime.substring(0, 4) + "年" + startTime.substring(4, 6) + "月";
        params.put(keyPrefix + "1_startTime", hz_startTime);

        String sql = "SELECT sum(periods_fee) c FROM " + gpSchema + ".f_idc_delayed_billing WHERE " + commonWhere;
        Object count = QueryHelper.selectColumn(connection, sql, "c");
        params.put(keyPrefix + "1_1", count);

        //全省总金额
        sql = "SELECT sum(periods_fee) c FROM " + gpSchema + ".f_idc_delayed_billing WHERE date BETWEEN " + startTime + " AND " + endTime;
        Object totalCount = QueryHelper.selectColumn(connection, sql, "c");


        //表格，已销户仍占用端口数量排名前5的社区（5级地址）：
        for (int i = 1; i <= 22; i++) {
            params.put(keyPrefix + "2_" + i, "-");
        }
        sql = "SELECT group_name,product_name,sum(periods_fee) c_periods from " + gpSchema + ".f_idc_delayed_billing WHERE " + commonWhere + "  GROUP BY group_name,product_name ORDER BY c_periods DESC LIMIT 5";
        List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);

        Double total = 0D;
        for (int i = 0; i < maps.size(); i++) {
            params.put(keyPrefix + "2_" + (i * 4 + 1), maps.get(i).get("group_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 2), maps.get(i).get("product_name"));
            params.put(keyPrefix + "2_" + (i * 4 + 3), maps.get(i).get("c_periods"));
            params.put(keyPrefix + "2_" + (i * 4 + 4), ObjectUtil.calcPercent(maps.get(i).get("c_periods"), totalCount) + "%");
            total += ObjectUtil.obj2Double(maps.get(i).get("c_periods"));
        }
        DecimalFormat df = new DecimalFormat("0.00");
        params.put(keyPrefix + "2_21", df.format(total));
        params.put(keyPrefix + "2_22", ObjectUtil.calcPercent(total, totalCount) + "%");
    }



    /**
     * 获取要被删除的段落标签
     *
     * @param datas
     * @param xwpfDocument
     * @return
     */
    public static List<String> getRemoveParagraphTarget(XWPFDocument xwpfDocument, Map<String, Object> datas) {
        List<String> paragraphTargets = getParagraphTarget(xwpfDocument);
        for (String key : datas.keySet()) {
            if (paragraphTargets.contains(key)) {
                paragraphTargets.remove(key);
            }
        }
        return paragraphTargets;
    }

    /**
     * 获取段落标签
     *
     * @return
     */
    public static List<String> getParagraphTarget(XWPFDocument xwpfDocument) {
        List<String> targets = new ArrayList<>();
        List<XWPFParagraph> paragraphs = xwpfDocument.getParagraphs();
        String regex = "^<!--(.+)-->$";
        Pattern pattern = Pattern.compile(regex);
        for (XWPFParagraph paragraph : paragraphs) {
            String text = paragraph.getText();
            Matcher matcher = pattern.matcher(text);
            if (matcher.find()) {
                String group = matcher.group(1);
                if (!targets.contains(group)) {
                    targets.add(group);
                }

            }
        }
        return targets;
    }

    /**
     * 删除多个段落
     *
     * @param xwpfDocument
     * @param keys
     */
    public static void remove(XWPFDocument xwpfDocument, List<String> keys) {

        //删除段落
        for (String key : keys) {
            remove(xwpfDocument, key);
        }

        //删除标签
        List<XWPFParagraph> removeElements = new ArrayList<>();
        String regex = "^<!--(.+)-->$";
        Pattern pattern = Pattern.compile(regex);
        for (XWPFParagraph paragraph : xwpfDocument.getParagraphs()) {
            String text = paragraph.getText();
            Matcher matcher = pattern.matcher(text);
            if (matcher.find()) {
                removeElements.add(paragraph);
            }
        }
        for (XWPFParagraph removeElement : removeElements) {
            xwpfDocument.removeBodyElement(xwpfDocument.getPosOfParagraph(removeElement));
        }
    }

    /**
     * 删除段落
     *
     * @param xwpfDocument
     * @param key
     */
    public static void remove(XWPFDocument xwpfDocument, String key) {

        List<IBodyElement> removeElements = new ArrayList<>();
        List<IBodyElement> bodyElements = xwpfDocument.getBodyElements();
        boolean isDelete = false;
        for (IBodyElement bodyElement : bodyElements) {
            switch (bodyElement.getElementType()) {
                case PARAGRAPH:
                    XWPFParagraph xwpfParagraph = (XWPFParagraph) bodyElement;
                    String text = xwpfParagraph.getText();
                    if (text.contains("<!--" + key + "-->")) {
                        isDelete = !isDelete;
                    }
                    break;
            }
            if (isDelete) {
                removeElements.add(bodyElement);
            }
        }

        removeElements = removeElements.stream().distinct().collect(Collectors.toList());
        for (IBodyElement bodyElement : removeElements) {
            switch (bodyElement.getElementType()) {
                case PARAGRAPH:
                    XWPFParagraph xwpfParagraph = (XWPFParagraph) bodyElement;
                    xwpfDocument.removeBodyElement(xwpfDocument.getPosOfParagraph(xwpfParagraph));
                    break;
                case TABLE:
                    XWPFTable table = (XWPFTable) bodyElement;
                    xwpfDocument.removeBodyElement(xwpfDocument.getPosOfTable(table));
                    break;
            }

        }

    }
}
